from celery import current_app
from flask import (
    Blueprint,
    jsonify,
    send_file,
    request,
    abort,
    make_response,
    redirect,
    url_for,
)
from datetime import date, datetime, timedelta
import io
import pandas as pd
import zipfile
from .models import (
    ScheduleResult,
    Elderdemand,
    TimeSlot,
    DicNursingAction,
    Attendantskills,
    CaregiverSchedule,
    ShiftAssignment,
    db,
)
from .scheduler import Plan
from .three_shift import ThreeShiftScheduler
from config_params import params
from sqlalchemy import func
from .redis_utils import get_redis_connection
from .redis_utils import get_update_time
from werkzeug.utils import secure_filename
import tempfile
import os
import json
from sqlalchemy.exc import SQLAlchemyError

main = Blueprint("main", __name__)


@main.route("/")
def index():
    return "Welcome to the Nursing Schedule App!"


@main.route("/check_update", methods=["GET"])
def check_update():
    redis_conn = get_redis_connection()
    if redis_conn:
        update_value = redis_conn.get(current_app.config["REDIS_UPDATE_KEY"])
        return jsonify({"update": update_value})
    else:
        return jsonify({"error": "无法连接到Redis"}), 500


@main.route("/last_update_time", methods=["GET"])
def get_last_update_time():
    last_update_time = get_update_time()
    if last_update_time:
        return jsonify({"last_update_time": last_update_time})
    else:
        return jsonify({"error": "无法获取最后更新时间"}), 404


@main.route("/generate_and_save_schedule", methods=["GET", "POST"])
def generate_and_save_schedule():
    plan = Plan(params)
    plan.run()  # 运行排班算法并保存结果

    if request.method == "POST":
        return jsonify(
            {
                "message": "Schedules generated and saved successfully",
                "date": datetime.now().isoformat(),
            }
        )
    else:
        return redirect(url_for("main.view_schedule"))


@main.route("/caregiver_schedule/<string:caregiver_id>", methods=["GET"])
def get_caregiver_schedule(caregiver_id):
    schedule_date_str = request.args.get("date", date.today().isoformat())
    schedule_date = datetime.strptime(schedule_date_str, "%Y-%m-%d").date()

    schedules = (
        CaregiverSchedule.query.filter(
            CaregiverSchedule.caregiver_id == caregiver_id,
            func.date(CaregiverSchedule.schedule_date) == schedule_date,
        )
        .order_by(CaregiverSchedule.time_slot)
        .all()
    )

    return jsonify(
        [
            {"time_slot": schedule.time_slot, "is_available": schedule.is_available}
            for schedule in schedules
        ]
    )


@main.route("/update_caregiver_availability", methods=["POST"])
def update_caregiver_availability():
    data = request.json
    caregiver_id = data.get("caregiver_id")
    schedule_date_str = data.get("date")
    time_slot = data.get("time_slot")
    is_available = data.get("is_available")

    if not all(
        [
            caregiver_id,
            schedule_date_str,
            time_slot is not None,
            is_available is not None,
        ]
    ):
        return jsonify({"error": "Missing required data"}), 400

    schedule_date = datetime.strptime(schedule_date_str, "%Y-%m-%d").date()

    schedule = CaregiverSchedule.query.filter_by(
        caregiver_id=caregiver_id, schedule_date=schedule_date, time_slot=time_slot
    ).first()

    if schedule:
        schedule.is_available = is_available
    else:
        new_schedule = CaregiverSchedule(
            caregiver_id=caregiver_id,
            schedule_date=schedule_date,
            time_slot=time_slot,
            is_available=is_available,
        )
        db.session.add(new_schedule)

    try:
        db.session.commit()
        return jsonify({"message": "Caregiver availability updated successfully"}), 200
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500


@main.route("/view_schedule", methods=["GET"])
def view_schedule():
    elder = request.args.get("elder")
    schedule_date_str = request.args.get("date", date.today().isoformat())
    schedule_date = datetime.strptime(schedule_date_str, "%Y-%m-%d").date()

    def process_time_slot(ts):
        return {
            "id": ts.id,
            "elder": ts.elder,
            "time_slot": ts.time_slot,
            "activity": ts.activity,
            "activity_id": ts.activity_id,
            "caregiver": "lack" if ts.caregiver_id == "-1" else ts.caregiver,
            "caregiver_id": ts.caregiver_id,
            "schedule_date": ts.schedule_date.isoformat(),
        }

    try:
        if elder:
            schedule_result = ScheduleResult.query.filter(
                ScheduleResult.elder == elder,
                db.func.date(ScheduleResult.schedule_date) == schedule_date,
            ).first()
            if schedule_result:
                time_slots = (
                    TimeSlot.query.filter_by(schedule_result_id=schedule_result.id)
                    .order_by(TimeSlot.time_order)
                    .all()
                )
                return jsonify(
                    {
                        "elder": elder,
                        "schedule_date": schedule_date.isoformat(),
                        "json_data": schedule_result.schedule_data,
                        "time_slots": [process_time_slot(ts) for ts in time_slots],
                    }
                )
            else:
                return jsonify({"error": "Schedule not found"}), 404
        else:
            all_schedules = ScheduleResult.query.filter(
                db.func.date(ScheduleResult.schedule_date) == schedule_date
            ).all()
            grouped_schedules = {}
            for sr in all_schedules:
                elder = sr.elder
                time_slots = (
                    TimeSlot.query.filter_by(schedule_result_id=sr.id)
                    .order_by(TimeSlot.time_order)
                    .all()
                )
                grouped_schedules[elder] = {
                    "schedule_date": sr.schedule_date.isoformat(),
                    "json_data": sr.schedule_data,
                    "time_slots": [process_time_slot(ts) for ts in time_slots],
                }
            return jsonify(grouped_schedules)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@main.route("/download_schedule/<string:elder>", methods=["GET"])
def download_schedule(elder):
    schedule_date_str = request.args.get("date", date.today().isoformat())
    schedule_date = datetime.strptime(schedule_date_str, "%Y-%m-%d").date()

    schedule_result = ScheduleResult.query.filter(
        ScheduleResult.elder == elder,
        func.date(ScheduleResult.schedule_date) == schedule_date,
    ).first()

    if not schedule_result:
        abort(404, description="Schedule not found")

    time_slots = (
        TimeSlot.query.filter_by(schedule_result_id=schedule_result.id)
        .order_by(TimeSlot.time_order)
        .all()
    )

    df = pd.DataFrame(
        [
            (
                ts.time_slot,
                ts.elder,
                ts.activity,
                ts.activity_id,
                "lack" if ts.caregiver_id == "-1" else ts.caregiver,
                ts.caregiver_id,
                ts.schedule_date,
            )
            for ts in time_slots
        ],
        columns=[
            "TimeSlot",
            "Elder",
            "Activity",
            "ActivityId",
            "Caregiver",
            "CaregiverId",
            "ScheduleDate",
        ],
    )

    excel_buffer = io.BytesIO()
    df.to_excel(excel_buffer, index=False)
    excel_buffer.seek(0)

    return send_file(
        excel_buffer,
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        as_attachment=True,
        download_name=f"{elder}_schedule_{schedule_date.isoformat()}.xlsx",
    )


@main.route("/download_all_schedules", methods=["GET"])
def download_all_schedules():
    schedule_date_str = request.args.get("date", date.today().isoformat())
    schedule_date = datetime.strptime(schedule_date_str, "%Y-%m-%d").date()
    schedule_results = ScheduleResult.query.filter(
        db.func.date(ScheduleResult.schedule_date) == schedule_date
    ).all()

    if not schedule_results:
        abort(404, description="No schedules found for the specified date")

    memory_file = io.BytesIO()
    with zipfile.ZipFile(memory_file, "w") as zf:
        for schedule_result in schedule_results:
            time_slots = (
                TimeSlot.query.filter_by(schedule_result_id=schedule_result.id)
                .order_by(TimeSlot.time_order)
                .all()
            )
            df = pd.DataFrame(
                [
                    (
                        ts.time_slot,
                        ts.activity,
                        "lack" if ts.caregiver_id == "-1" else ts.caregiver,
                    )
                    for ts in time_slots
                ],
                columns=["TimeSlot", "Activity", "Caregiver"],
            )
            excel_buffer = io.BytesIO()
            df.to_excel(excel_buffer, index=False)
            excel_buffer.seek(0)
            zf.writestr(
                f"{schedule_result.elder}_schedule.xlsx", excel_buffer.getvalue()
            )

    memory_file.seek(0)

    return send_file(
        memory_file,
        mimetype="application/zip",
        as_attachment=True,
        download_name=f"all_schedules_{schedule_date.isoformat()}.zip",
    )


@main.route("/nursing_actions", methods=["GET"])
def get_nursing_actions():
    actions = DicNursingAction.query.all()
    return jsonify(
        [
            {
                "id": action.id,
                "pid": action.pid,
                "name": action.name,
                "level": action.level,
                "nursing_level": action.nursing_level,
            }
            for action in actions
        ]
    )


@main.route("/elders", methods=["GET"])
def get_elders():
    elders = Elderdemand.query.filter_by(arrange="1").all()  # 只返回arrange为'1'的老人
    return jsonify(
        [
            {
                "id": elder.id,
                "elder": elder.elder,
                "actions": elder.actions,
                "arrange": elder.arrange,
            }
            for elder in elders
        ]
    )


@main.route("/caregivers", methods=["GET"])
def get_caregivers():
    caregivers = Attendantskills.query.all()
    return jsonify(
        [
            {
                "id": caregiver.id,
                "attendant_name": caregiver.attendant_name,
                "skills": caregiver.skills,
            }
            for caregiver in caregivers
        ]
    )


@main.route("/simple_three_shift_schedule", methods=["POST"])
def generate_simple_three_shift_schedule():
    """简化版三班制排班计划API，支持使用数字(1,2,3)作为班次名称

    接收三个关键参数：
    1. shift_mode: 一天分几段（班制数量），可选1、2、3，默认使用全局参数
    2. custom_shifts: 段数时间范围，可自定义各班次的时间范围
    3. time_interval_minutes: 一个小时分段数，一个小时按多少分钟分段（10-60，默认20分钟）

    示例请求：
    {
      "shift_mode": 3,
      "custom_shifts": [
        {"name": "1", "start_time": "07:00", "end_time": "15:00"},
        {"name": "2", "start_time": "15:00", "end_time": "23:00"},
        {"name": "3", "start_time": "23:00", "end_time": "07:00"}
      ],
      "time_interval_minutes": 20
    }
    """
    try:
        # 记录请求内容
        import logging
        from flask import jsonify  # 确保导入jsonify
        import config_params  # 导入全局参数
        from .shift_params_processor import ShiftParamsProcessor  # 导入参数处理器

        logger = logging.getLogger(__name__)
        logger.info(f"接收到简化版三班制排班请求: {request.data}")

        # 尝试解析JSON
        try:
            if request.data:
                data = request.get_json(force=True)
                logger.info(f"成功解析JSON数据: {data}")
            else:
                data = {}
                logger.warning("请求体为空，使用默认参数")
        except Exception as json_error:
            logger.error(f"JSON解析错误: {str(json_error)}")
            # 当JSON解析失败时，使用空字典继续执行
            data = {}
            logger.warning("由于JSON解析错误，使用默认参数继续执行")

        # 使用参数处理器处理参数
        processed_data = ShiftParamsProcessor.process_shift_params(data, config_params.three_shift_params)
        logger.info(f"处理后的参数: {processed_data}")

        # 确保生成照护时间线
        processed_data['generate_care_timeline'] = True

        # 调用原接口处理请求
        return generate_three_shift_schedule(processed_data)
    except Exception as e:
        import traceback
        error_traceback = traceback.format_exc()
        logger.error(f"简化版三班制排班计划生成失败: {str(e)}")
        logger.error(f"错误详情: {error_traceback}")
        return jsonify({
            "error": str(e),
            "traceback": error_traceback.split("\n") if error_traceback else None
        }), 500

@main.route("/three_shift_schedule", methods=["POST"])
def generate_three_shift_schedule(custom_data=None):
    """生成三班制排班计划API"""
    try:
        # 记录请求内容
        import logging
        from config_params import three_shift_params
        from .shift_params_processor import ShiftParamsProcessor  # 导入参数处理器

        logger = logging.getLogger(__name__)

        # 如果是从简化接口调用，使用传入的数据
        if custom_data is not None:
            data = custom_data
            logger.info(f"从简化接口接收到数据: {data}")
        else:
            logger.info(f"接收到三班制排班请求: {request.data}")

            # 尝试解析JSON
            try:
                if request.data:
                    data = request.get_json(force=True)
                    logger.info(f"成功解析JSON数据: {data}")
                else:
                    data = {}
                    logger.warning("请求体为空，使用默认参数")
            except Exception as json_error:
                logger.error(f"JSON解析错误: {str(json_error)}")
                # 当JSON解析失败时，使用空字典继续执行
                data = {}
                logger.warning("由于JSON解析错误，使用默认参数继续执行")

            # 如果是直接调用接口（不是从简化接口调用），使用参数处理器处理参数
            if not isinstance(data, dict) or not data.get('processed_by_shift_params_processor'):
                data = ShiftParamsProcessor.process_shift_params(data, three_shift_params)
                logger.info(f"处理后的参数: {data}")
                # 标记参数已经被处理
                data['processed_by_shift_params_processor'] = True

        # 检查是否需要立即执行自动排班
        auto_schedule_enabled = data.get('auto_schedule_enabled', three_shift_params.get('auto_schedule_enabled', False))
        auto_schedule_immediate = data.get('auto_schedule_immediate', three_shift_params.get('auto_schedule_immediate', True))

        if auto_schedule_enabled and auto_schedule_immediate:
            logger.info("检测到自动排班参数，将在接口调用后立即执行一次自动排班")

        # 使用全局参数作为默认值
        from config_params import three_shift_params

        # 获取请求参数（如果提供，则覆盖全局参数）
        schedule_type = data.get(
            "schedule_type", three_shift_params.get("schedule_type", 1)
        )
        num_days = int(data.get("num_days", three_shift_params.get("num_days", 1)))
        start_date = data.get("start_date", three_shift_params.get("start_date"))
        shift_start_time = data.get(
            "shift_start_time", three_shift_params.get("shift_start_time")
        )

        # 班制控制参数
        shift_mode = data.get("shift_mode")  # 班制模式
        custom_shifts = data.get("custom_shifts")  # 自定义班次
        enabled_shifts = data.get("enabled_shifts")  # 启用的班次
        disabled_shifts = data.get("disabled_shifts")  # 禁用的班次
        auto_adjust_time = data.get("auto_adjust_time")  # 是否自动调整班次时间

        # 休息时间控制参数
        min_rest_between_shifts = data.get(
            "min_rest_between_shifts"
        )  # 班次间最小休息时间
        min_rest_after_night_shift = data.get(
            "min_rest_after_night_shift"
        )  # 夜班后最小休息时间
        max_consecutive_shifts = data.get(
            "max_consecutive_shifts"
        )  # 最大连续工作班次数

        # 验证排班类型
        valid_schedule_types_str = ["daily", "weekly", "monthly", "quarterly", "yearly"]
        valid_schedule_types_int = [1, 2, 3, 4, 5]

        # 如果是字符串类型
        if (
            isinstance(schedule_type, str)
            and schedule_type not in valid_schedule_types_str
        ):
            return (
                jsonify(
                    {
                        "error": f"不支持的排班类型: {schedule_type}。支持的类型: {', '.join(valid_schedule_types_str)}"
                    }
                ),
                400,
            )
        # 如果是数字类型
        elif (
            isinstance(schedule_type, int)
            and schedule_type not in valid_schedule_types_int
        ):
            return (
                jsonify(
                    {
                        "error": f"不支持的排班类型数字: {schedule_type}。支持的类型: 1(按天), 2(按周), 3(按月), 4(按季度), 5(按年)"
                    }
                ),
                400,
            )

        # 将数字类型转换为字符串类型（与原有代码兼容）
        schedule_type_map = {
            1: "daily",
            2: "weekly",
            3: "monthly",
            4: "quarterly",
            5: "yearly",
        }

        # 如果是数字类型，转换为字符串
        if isinstance(schedule_type, int):
            schedule_type = schedule_type_map.get(schedule_type)

        # 验证天数参数（仅当排班类型为daily时需要）
        if schedule_type == "daily" and (num_days < 1 or num_days > 365):
            return jsonify({"error": "天数参数必须在1到365之间"}), 400

        # 验证开始日期格式
        if start_date:
            try:
                datetime.strptime(start_date, "%Y-%m-%d")
            except ValueError:
                return jsonify({"error": "开始日期格式无效，请使用YYYY-MM-DD格式"}), 400

        # 验证班次开始时间格式
        if shift_start_time:
            try:
                datetime.strptime(shift_start_time, "%H:%M")
            except ValueError:
                return jsonify({"error": "班次开始时间格式无效，请使用HH:MM格式"}), 400

        # 实例化三班制排班调度器
        scheduler = ThreeShiftScheduler()

        # 如果提供了班制模式，设置班制模式
        if shift_mode:
            scheduler.set_shift_mode(
                mode=shift_mode,
                custom_shifts=custom_shifts,
                start_time=shift_start_time,
            )

        # 如果提供了启用班次列表，更新班次配置
        if enabled_shifts:
            scheduler.update_shift_config(enabled_shifts=enabled_shifts)

        # 如果提供了禁用班次列表，设置禁用班次
        if disabled_shifts:
            scheduler.disable_shifts(disabled_shifts)

        # 如果提供了自动调整时间参数，更新班次控制参数
        if auto_adjust_time is not None:
            scheduler.update_shift_control(auto_adjust_time=auto_adjust_time)

        # 如果提供了休息时间控制参数，更新休息时间控制参数
        if (
            min_rest_between_shifts is not None
            or min_rest_after_night_shift is not None
            or max_consecutive_shifts is not None
        ):
            # 更新休息时间控制参数
            if min_rest_between_shifts is not None:
                scheduler.rest_time_control["min_rest_between_shifts"] = (
                    min_rest_between_shifts
                )

            if min_rest_after_night_shift is not None:
                scheduler.rest_time_control["min_rest_after_night_shift"] = (
                    min_rest_after_night_shift
                )

            if max_consecutive_shifts is not None:
                scheduler.rest_time_control["max_consecutive_shifts"] = (
                    max_consecutive_shifts
                )

        # 生成并保存排班计划
        logger.info(
            f"开始生成排班计划: schedule_type={schedule_type}, num_days={num_days}, start_date={start_date}"
        )

        # 添加generate_care_timeline参数
        generate_care_timeline = data.get(
            "generate_care_timeline",
            three_shift_params.get("generate_care_timeline", True),
        )
        logger.info(f"是否生成照护时间线: {generate_care_timeline}")

        # 新业务0414: 获取新参数
        day_segments = data.get("day_segments", three_shift_params.get("day_segments", 3))
        logger.info(f"一天分几段: {day_segments}")

        first_segment_start = data.get("first_segment_start", three_shift_params.get("first_segment_start", "06:00"))
        logger.info(f"第一段开始时间: {first_segment_start}")

        first_segment_end = data.get("first_segment_end", three_shift_params.get("first_segment_end", "14:00"))
        logger.info(f"第一段结束时间: {first_segment_end}")

        param_priority_enabled = data.get("param_priority_enabled", three_shift_params.get("param_priority_enabled", True))
        logger.info(f"传参优先级开关: {param_priority_enabled}")

        # 验证新参数
        if day_segments is not None:
            try:
                day_segments = int(day_segments)
                if day_segments < 1 or day_segments > 3:
                    return jsonify({"error": "一天分段数量必须在1-3之间"}), 400
            except ValueError:
                return jsonify({"error": "一天分段数量必须是整数"}), 400

        if first_segment_start is not None:
            try:
                datetime.strptime(first_segment_start, "%H:%M")
            except ValueError:
                return jsonify({"error": "第一段开始时间格式无效，请使用HH:MM格式"}), 400

        if first_segment_end is not None:
            try:
                datetime.strptime(first_segment_end, "%H:%M")
            except ValueError:
                return jsonify({"error": "第一段结束时间格式无效，请使用HH:MM格式"}), 400

        # 获取时间槽参数
        time_interval_minutes = data.get("time_interval_minutes", three_shift_params.get("time_interval_minutes", 20))

        # 验证并转换time_interval_minutes参数
        try:
            # 尝试转换为整数
            time_interval_minutes = int(time_interval_minutes)

            # 验证参数范围
            if time_interval_minutes < 10 or time_interval_minutes > 60:
                logger.warning(f"时间间隔参数({time_interval_minutes})超出有效范围(10-60)，使用默认值20分钟")
                time_interval_minutes = 20  # 默认值
            else:
                logger.info(f"使用有效的时间间隔: {time_interval_minutes}分钟")
        except (ValueError, TypeError):
            logger.warning(f"无效的时间间隔参数类型: {type(time_interval_minutes).__name__}，使用默认值20分钟")
            time_interval_minutes = 20  # 默认值

        # 计算时间槽数量
        time_slots_per_day = 24 * 60 // time_interval_minutes
        logger.info(f"根据时间间隔 {time_interval_minutes} 分钟计算出的时间槽数量: {time_slots_per_day}")

        result = scheduler.generate_and_save_schedule(
            schedule_type=schedule_type,  # 已经在前面转换为字符串类型
            num_days=num_days,
            start_date=start_date,
            shift_start_time=shift_start_time,
            generate_care_timeline=generate_care_timeline,
            shift_mode=shift_mode,
            custom_shifts=custom_shifts,
            time_slots_per_day=time_slots_per_day,
            time_interval_minutes=time_interval_minutes,
            day_segments=day_segments,
            first_segment_start=first_segment_start,
            first_segment_end=first_segment_end,
            param_priority_enabled=param_priority_enabled,
        )

        logger.info(f"排班计划生成结果: {result}")

        # 如果需要立即执行自动排班，在后台启动一个线程执行
        if auto_schedule_enabled and auto_schedule_immediate:
            import threading
            from .three_shift import auto_schedule

            def run_auto_schedule():
                logger.info("开始执行自动排班...")
                try:
                    auto_schedule()
                    logger.info("自动排班执行完成")
                except Exception as e:
                    logger.error(f"自动排班执行失败: {str(e)}")

            # 启动一个新线程执行自动排班，不阻塞当前请求
            thread = threading.Thread(target=run_auto_schedule)
            thread.daemon = True  # 设置为守护线程，不阻止主程序退出
            thread.start()
            logger.info("自动排班线程已启动")

        return jsonify(result)
    except Exception as e:
        import traceback

        error_traceback = traceback.format_exc()
        logger.error(f"排班计划生成失败: {str(e)}")
        logger.error(f"错误详情: {error_traceback}")
        return (
            jsonify(
                {
                    "error": str(e),
                    "traceback": (
                        error_traceback.split("\n") if error_traceback else None
                    ),
                }
            ),
            500,
        )


@main.route("/three_shift_schedule", methods=["GET"])
def get_three_shift_schedule():
    """获取三班制排班计划API"""
    try:
        # 获取请求参数
        start_date_str = request.args.get("start_date")
        end_date_str = request.args.get("end_date")
        caregiver_id = request.args.get("caregiver_id")

        # 处理日期参数
        if start_date_str:
            try:
                start_date = datetime.strptime(start_date_str, "%Y-%m-%d").date()
            except ValueError:
                return jsonify({"error": "开始日期格式无效，请使用YYYY-MM-DD格式"}), 400
        else:
            # 默认为今天
            start_date = date.today()

        if end_date_str:
            try:
                end_date = datetime.strptime(end_date_str, "%Y-%m-%d").date()
            except ValueError:
                return jsonify({"error": "结束日期格式无效，请使用YYYY-MM-DD格式"}), 400
        else:
            # 默认为今天
            end_date = start_date

        # 验证日期范围
        if end_date < start_date:
            return jsonify({"error": "结束日期不能早于开始日期"}), 400

        # 查询排班数据
        if caregiver_id:
            # 查询特定护理员的排班，日期降序排序
            assignments = ShiftAssignment.get_assignments_by_caregiver(
                caregiver_id, start_date, end_date, date_desc=True
            )
        else:
            # 查询所有排班，日期降序排序
            assignments = ShiftAssignment.get_assignments_by_date_range(
                start_date, end_date, date_desc=True
            )

        # 构建响应数据
        result = {
            "start_date": start_date.isoformat(),
            "end_date": end_date.isoformat(),
            "total_assignments": len(assignments),
            "assignments": [],
        }

        for assignment in assignments:
            result["assignments"].append(
                {
                    "id": assignment.id,
                    "caregiver_id": assignment.caregiver_id,
                    "caregiver_name": assignment.caregiver_name,
                    "shift_date": assignment.shift_date.isoformat(),
                    "shift_type": assignment.shift_type,
                    "shift_start_time": assignment.shift_start_time.strftime("%H:%M"),
                    "shift_end_time": assignment.shift_end_time.strftime("%H:%M"),
                    "schedule_type": assignment.schedule_type,
                }
            )

        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@main.route("/elder_schedule/<string:elder_id>", methods=["GET"])
def get_elder_schedule(elder_id):
    """获取老人的详细排班信息"""
    try:
        # 获取请求参数
        date_str = request.args.get("date", date.today().isoformat())

        # 处理日期参数
        try:
            schedule_date = datetime.strptime(date_str, "%Y-%m-%d").date()
        except ValueError:
            return jsonify({"error": "日期格式无效，请使用YYYY-MM-DD格式"}), 400

        # 查询老人的排班结果
        schedule_result = ScheduleResult.query.filter_by(
            elder=elder_id, schedule_date=schedule_date
        ).first()

        if not schedule_result:
            return (
                jsonify({"error": f"未找到老人 {elder_id} 在 {date_str} 的排班信息"}),
                404,
            )

        # 查询时间段安排
        time_slots = (
            TimeSlot.query.filter_by(schedule_result_id=schedule_result.id)
            .order_by(TimeSlot.time_order)
            .all()
        )

        # 构建响应数据
        result = {
            "elder_id": elder_id,
            "schedule_date": schedule_date.isoformat(),
            "schedule_data": schedule_result.schedule_data,
            "time_slots": [],
        }

        for slot in time_slots:
            result["time_slots"].append(
                {
                    "time_slot": slot.time_slot,
                    "activity": slot.activity,
                    "activity_id": slot.activity_id,
                    "caregiver": slot.caregiver,
                    "caregiver_id": slot.caregiver_id,
                    "time_order": slot.time_order,
                }
            )

        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@main.route("/download_three_shift_schedule", methods=["GET"])
def download_three_shift_schedule():
    """下载三班制排班计划"""
    try:
        # 获取请求参数
        start_date_str = request.args.get("start_date")
        end_date_str = request.args.get("end_date")

        # 处理日期参数
        if start_date_str:
            try:
                start_date = datetime.strptime(start_date_str, "%Y-%m-%d").date()
            except ValueError:
                return jsonify({"error": "开始日期格式无效，请使用YYYY-MM-DD格式"}), 400
        else:
            # 默认为今天
            start_date = date.today()

        if end_date_str:
            try:
                end_date = datetime.strptime(end_date_str, "%Y-%m-%d").date()
            except ValueError:
                return jsonify({"error": "结束日期格式无效，请使用YYYY-MM-DD格式"}), 400
        else:
            # 默认为今天
            end_date = start_date

        # 验证日期范围
        if end_date < start_date:
            return jsonify({"error": "结束日期不能早于开始日期"}), 400

        # 查询排班数据
        assignments = ShiftAssignment.get_assignments_by_date_range(
            start_date, end_date
        )

        if not assignments:
            return jsonify({"error": "指定日期范围内没有排班数据"}), 404

        # 创建DataFrame
        data = []
        for a in assignments:
            data.append(
                {
                    "护理员ID": a.caregiver_id,
                    "护理员姓名": a.caregiver_name,
                    "排班日期": a.shift_date.isoformat(),
                    "班次类型": a.shift_type,
                    "开始时间": a.shift_start_time.strftime("%H:%M"),
                    "结束时间": a.shift_end_time.strftime("%H:%M"),
                    "排班类型": a.schedule_type,
                }
            )

        df = pd.DataFrame(data)

        # 生成Excel文件
        excel_buffer = io.BytesIO()
        df.to_excel(excel_buffer, index=False)
        excel_buffer.seek(0)

        return send_file(
            excel_buffer,
            mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            as_attachment=True,
            download_name=f"three_shift_schedule_{start_date.isoformat()}_to_{end_date.isoformat()}.xlsx",
        )
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@main.route("/import_excel", methods=["POST"])
def import_excel():
    """
    导入Excel文件生成照护时间线
    接收Excel文件，读取护理员和老人信息，调用三班排班接口生成照护时间线
    """
    # 检查是否有文件上传
    if 'file' not in request.files:
        return jsonify({'status': 'error', 'message': '未找到上传的文件'}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'status': 'error', 'message': '未选择文件'}), 400
    
    # 检查文件类型
    if not file.filename.endswith(('.xlsx', '.xls')):
        return jsonify({'status': 'error', 'message': '请上传Excel文件（.xlsx或.xls格式）'}), 400
    
    try:
        # 读取Excel文件
        xls = pd.ExcelFile(file)
        
        # 检查必要的工作表是否存在
        required_sheets = ['护理员信息', '老人信息']
        missing_sheets = [sheet for sheet in required_sheets if sheet not in xls.sheet_names]
        if missing_sheets:
            return jsonify({
                'status': 'error',
                'message': f'缺少必要的工作表: {", ".join(missing_sheets)}'
            }), 400
        
        # 读取护理员信息
        caregivers_df = pd.read_excel(xls, '护理员信息')
        if caregivers_df.empty:
            return jsonify({'status': 'error', 'message': '护理员信息不能为空'}), 400
        
        # 读取老人信息
        elders_df = pd.read_excel(xls, '老人信息')
        if elders_df.empty:
            return jsonify({'status': 'error', 'message': '老人信息不能为空'}), 400
        
        # 检查必要的列是否存在
        caregiver_required_columns = ['护理员ID', '姓名']
        caregiver_missing_columns = [col for col in caregiver_required_columns if col not in caregivers_df.columns]
        if caregiver_missing_columns:
            return jsonify({
                'status': 'error',
                'message': f'护理员信息表缺少必要的列: {", ".join(caregiver_missing_columns)}'
            }), 400
        
        elder_required_columns = ['老人ID', '姓名']
        elder_missing_columns = [col for col in elder_required_columns if col not in elders_df.columns]
        if elder_missing_columns:
            return jsonify({
                'status': 'error',
                'message': f'老人信息表缺少必要的列: {", ".join(elder_missing_columns)}'
            }), 400
        
        # 获取排班参数
        schedule_date = request.form.get('schedule_date', datetime.now().strftime('%Y-%m-%d'))
        shift_mode = int(request.form.get('shift_mode', 3))
        time_interval_minutes = int(request.form.get('time_interval_minutes', 20))
        
        # 检查时间间隔是否在有效范围内
        if time_interval_minutes < 10 or time_interval_minutes > 60:
            time_interval_minutes = 20  # 使用默认值
        
        # 准备自定义班次信息
        custom_shifts = []
        if 'custom_shifts' in request.form:
            try:
                custom_shifts = json.loads(request.form['custom_shifts'])
            except json.JSONDecodeError:
                # 如果解析失败，使用默认班次
                custom_shifts = []
        
        # 如果Excel中包含班次信息，则读取
        if '班次信息(可选)' in xls.sheet_names and not custom_shifts:
            shifts_df = pd.read_excel(xls, '班次信息(可选)')
            if not shifts_df.empty and all(col in shifts_df.columns for col in ['班次名称', '开始时间', '结束时间']):
                custom_shifts = []
                for idx, row in shifts_df.iterrows():
                    if idx < shift_mode:  # 只处理需要的班次数量
                        custom_shifts.append({
                            'name': row['班次名称'],
                            'start_time': row['开始时间'],
                            'end_time': row['结束时间']
                        })
        
        # 将数据保存到数据库
        try:
            db.session.begin()
            
            # 处理护理员数据
            from app.models import Caregiver
            for _, row in caregivers_df.iterrows():
                caregiver_id = int(row['护理员ID'])
                caregiver = Caregiver.query.filter_by(caregiver_id=caregiver_id).first()
                
                if not caregiver:
                    caregiver = Caregiver(caregiver_id=caregiver_id)
                
                caregiver.caregiver_name = row['姓名']
                if '技能等级' in row:
                    caregiver.skill_level = int(row['技能等级']) if not pd.isna(row['技能等级']) else 3
                if '偏好班次' in row:
                    caregiver.shift_preference = row['偏好班次'] if not pd.isna(row['偏好班次']) else '无'
                
                db.session.add(caregiver)
            
            # 处理老人数据
            from app.models import Elder
            for _, row in elders_df.iterrows():
                elder_id = int(row['老人ID'])
                elder = Elder.query.filter_by(elder_id=elder_id).first()
                
                if not elder:
                    elder = Elder(elder_id=elder_id)
                
                elder.elder_name = row['姓名']
                if '自理能力评级' in row:
                    elder.self_care_level = int(row['自理能力评级']) if not pd.isna(row['自理能力评级']) else 3
                if '特殊照护需求' in row:
                    elder.special_needs = row['特殊照护需求'] if not pd.isna(row['特殊照护需求']) else '无'
                
                db.session.add(elder)
            
            db.session.commit()
        except SQLAlchemyError as e:
            db.session.rollback()
            return jsonify({'status': 'error', 'message': f'数据库操作失败: {str(e)}'}), 500
        
        # 准备调用三班排班接口的参数
        scheduler_params = {
            'schedule_date': schedule_date,
            'shift_mode': shift_mode,
            'time_interval_minutes': time_interval_minutes
        }
        
        if custom_shifts:
            scheduler_params['custom_shifts'] = custom_shifts
        
        # 调用三班排班接口生成照护时间线
        try:
            scheduler = ThreeShiftScheduler()
            batch_number, result = scheduler.generate_and_save_schedule(**scheduler_params)
            
            if result.get('status') == 'success':
                return jsonify({
                    'status': 'success',
                    'message': '成功导入数据并生成照护时间线',
                    'batch_number': batch_number,
                    'result': result
                })
            else:
                return jsonify({
                    'status': 'error',
                    'message': '生成照护时间线失败',
                    'details': result.get('message', '未知错误')
                }), 500
        except Exception as e:
            return jsonify({'status': 'error', 'message': f'排班过程发生错误: {str(e)}'}), 500
    
    except Exception as e:
        return jsonify({'status': 'error', 'message': f'处理Excel文件时发生错误: {str(e)}'}), 500

@main.route('/download_schedule_results', methods=['GET'])
def download_schedule_results():
    """下载排班结果"""
    batch_number = request.args.get('batch_number')
    if not batch_number:
        return jsonify({'status': 'error', 'message': '未提供批次号'}), 400
    
    try:
        # 查询排班结果
        from app.models import ShiftAssignment, ScheduleResult, TimeSlot
        
        shift_assignments = ShiftAssignment.query.filter_by(batch_number=batch_number).all()
        schedule_results = ScheduleResult.query.filter_by(batch_number=batch_number).all()
        time_slots = TimeSlot.query.filter_by(batch_number=batch_number).all()
        
        if not shift_assignments and not schedule_results:
            return jsonify({'status': 'error', 'message': '未找到指定批次的排班结果'}), 404
        
        # 创建Excel文件
        output = io.BytesIO()
        writer = pd.ExcelWriter(output, engine='openpyxl')
        
        # 护理员排班表
        if shift_assignments:
            shift_data = [{
                '批次号': sa.batch_number,
                '日期': sa.schedule_date.strftime('%Y-%m-%d') if sa.schedule_date else '',
                '护理员ID': sa.caregiver_id,
                '护理员姓名': sa.caregiver_name,
                '班次': sa.shift_name,
                '开始时间': sa.start_time,
                '结束时间': sa.end_time,
                '创建时间': sa.create_time.strftime('%Y-%m-%d %H:%M:%S') if sa.create_time else ''
            } for sa in shift_assignments]
            
            shift_df = pd.DataFrame(shift_data)
            shift_df.to_excel(writer, sheet_name='护理员排班表', index=False)
        
        # 照护计划总表
        if schedule_results:
            schedule_data = [{
                '批次号': sr.batch_number,
                '日期': sr.plan_date.strftime('%Y-%m-%d') if sr.plan_date else '',
                '老人ID': sr.elder_id,
                '老人姓名': sr.elder_name if hasattr(sr, 'elder_name') else '',
                '时间段数量': sr.slots_count,
                '创建时间': sr.create_time.strftime('%Y-%m-%d %H:%M:%S') if sr.create_time else ''
            } for sr in schedule_results]
            
            schedule_df = pd.DataFrame(schedule_data)
            schedule_df.to_excel(writer, sheet_name='照护计划总表', index=False)
        
        # 照护计划分表
        if time_slots:
            slot_data = [{
                '批次号': ts.batch_number,
                '照护计划ID': ts.schedule_id,
                '老人ID': ts.elder_id,
                '时间段': ts.time_slot,
                '活动': ts.activity,
                '活动ID': ts.activity_id,
                '护理员': ts.caregiver_name,
                '护理员ID': ts.caregiver_id,
                '创建时间': ts.create_time.strftime('%Y-%m-%d %H:%M:%S') if ts.create_time else ''
            } for ts in time_slots]
            
            slot_df = pd.DataFrame(slot_data)
            slot_df.to_excel(writer, sheet_name='照护计划分表', index=False)
        
        # 保存Excel文件
        writer.close()
        
        # 设置文件指针到开始位置
        output.seek(0)
        
        # 返回Excel文件
        return send_file(
            output,
            as_attachment=True,
            download_name=f'照护排班结果_{batch_number}.xlsx',
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
    
    except Exception as e:
        return jsonify({'status': 'error', 'message': f'生成Excel文件时发生错误: {str(e)}'}), 500

@main.errorhandler(404)
def not_found(error):
    return make_response(jsonify({"error": str(error)}), 404)


def init_app(app):
    app.register_blueprint(main)

    # 通过POST请求到 /generate_and_save_schedule  生成新的调度并保存到数据库。
    # 通过GET请求到 /view_schedule查看保存的调度。
    # 通过GET请求到 /download_all_schedules或 /download_schedule/<elder_id> 下载保存的调度。

    # 照护计划id
    # 老人id
    # 时间段
    # 护理动作
    # 护理员
    # 更新时间
